package com.comm.db.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.HashMap;

import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;

public class JdbcUtil {
	/**
	 * mysql 配置
	 */
	static String My_SQL_SID = "car";// 

	static String My_SQL_dbUser = "root";// "QX10";

	static String My_SQL_dbPassword = "root";//"root";// "QX10" ;

	static String My_SQL_SIP = "localhost";//"172.26.6.53";//"172.26.6.53";

	static String My_SQL_port = "3306";

	static String My_SQL_url_discuz = "jdbc:mysql://" + My_SQL_SIP + ":"
			+ My_SQL_port + "/" + My_SQL_SID + "?characterEncoding=utf8";//jdbc:mysql://172.26.6.57:3306/discuz

	private static List<Connection> connList = new ArrayList<Connection>();

	private JdbcUtil() {

	}

	private static JdbcUtil jdbc = new JdbcUtil();

	public static synchronized JdbcUtil GetJdbc() {
		return jdbc;
	}

	static {

		String My_SQL_DriverString = "com.mysql.jdbc.Driver";
		try {

			Class.forName(My_SQL_DriverString).newInstance();
			for (int i = 0; i < 5; i++) {
				Connection conn = getConnection();
				connList.add(conn);
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("加载驱动出错");
		}

	}

	/**
	 * 取得 mysql connection
	 * @param mysql
	 * @return
	 */
	public static synchronized Connection getConnection() {

		if (connList.size() > 0)
			return connList.get(0);

		Connection _conn = getConn(My_SQL_url_discuz, My_SQL_dbUser,
				My_SQL_dbPassword);
		return _conn;
	}

	private static synchronized Connection getConn(String jdbcurl,
			String username, String pw) {
		try {
			Connection _conn = DriverManager.getConnection(jdbcurl, username,
					pw);
			return _conn;
		} catch (SQLException e) {

			e.printStackTrace();
			System.out.println("建立mysql连接失败");
		}
		return null;
	}

	int batchCNT = 0;

	public int excuteBathe(String sql, List list) {
		if (list == null || StringUtils.isEmpty(sql)) {
			return 0;
		}
		Connection con = getConnection();
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);

			int i = 1;
			for (; i <= list.size(); i++) {

				List temp = (List) list.get(i - 1);
				for (int k = 0; k < temp.size(); k++) {
					ps.setObject(k + 1, temp.get(k));
				}
				ps.addBatch();
				if (0 == i % 100) {

					System.out.println("i%10==>>i:" + i);
					int cnt[] = ps.executeBatch();
					//System.out.println("--------------------------------------------");
					// for(int j=0;j<cnt.length;j++){
					// System.out.println(cnt[j]);
					//} // System.out.println("--------------------------------------------");
				}
			}
			if (0 != i % 100) {

				System.out.println("last==>>i:" + i);
				int cnt[] = ps.executeBatch();
			}
			//con.commit();
			System.out.println("list.size==>>i:" + list.size());

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			connList.add(con);
			try {
				ps.clearBatch();
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	public Connection excute(String sql, List list) {
		Connection connection = null;
		PreparedStatement ps = null;
		try {

			// System.out.println(sql);
			connection = getConnection();

			ps = connection.prepareStatement(sql);

			// 设置参数
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
			}
			ps.execute();
			//connection.commit();

		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return null;
		} finally {

			try {
				connList.add(connection);
				ps.clearParameters();

			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}

		}
		return connection;
	}

	/**
	 * 通用分页:可以用于不同数据库 从数据库中查询所有记录，然后通过游标来获取所需maxCount条记录
	 * 
	 * @param sql
	 *            传入的sql语句
	 * @param list
	 *            参数
	 * @param startIndex
	 *            从哪一条记录开始
	 * 
	 * @param maxCount
	 *            总共取多少条记录
	 * 
	 */

	public List getList(String sql, List list, int startIndex, int maxCount) {
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rsSet = null;
		List listRS = new ArrayList<Map>();

		try {

			connection = getConnection();
			ps = connection.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			// 设置参数
			int i = 0;
			for (; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
			}
			// ps.setInt( ++i,startIndex+maxCount);
			// ps.setObject(++i,startIndex);
			/*
			 * 通用分页
			 */
			// 通用分页1
			ps.setMaxRows(startIndex + maxCount - 1);

			ps.executeQuery();
			rsSet = ps.getResultSet();
			/*
			 * 通用分页 //将游标移动到第一条记录 通用分页2 rsSet.first(); // 游标移动到要输出的第一条记录
			 * 
			 * 通用分页3 rsSet.relative(startIndex-2);
			 */
			rsSet.first();
			rsSet.relative(startIndex - 2);
			//createQuery(rsSet);

			try {

				int ColumnCount = rsSet.getMetaData().getColumnCount();
				// logger.info(" 列数 : " + ColumnCount);
				Map<String, Object> map = null;
				while (rsSet.next()) {
					map = new HashMap<String, Object>();
					for (int k = 1; k < (ColumnCount + 1); k++) {
						map.put("item" + k, rsSet.getObject(k));

					}
					listRS.add(map);

				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

			connection.commit();

		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList();
		} finally {
			try {
				connList.add(connection);
				rsSet.close();
				ps.close();
			} catch (SQLException e) {
				//   Auto-generated catch block
				e.printStackTrace();
			}

		}
		return listRS;
	}

	/**
	 * 分页调用的
	 * @param sql
	 * @param listparams
	 * @return
	 */
	private int countRowsPage(String sql, List listparams) {
		 sql = "select count(*) cnt from   (" + sql + ") ";
		 return count(sql, listparams);
    }

	private int count(String sql, List list) {
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {

			connection = getConnection();

			ps = connection.prepareStatement(sql);

			//设置参数
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
			}

			rs = ps.executeQuery();	 
            while(rs.next()){
            	return rs.getInt("cnt");
            }
		} catch (SQLException e) {
			e.printStackTrace();
			 
		} catch (Exception e) {
			e.printStackTrace();
			 
		} finally {
			try {
				if (null != connection)
					connList.add(connection);
				if (null != rs)
					rs.close();
				if (null != ps)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return 0;
	}

	public static List<Map<String, Object>> getListMap(String sql, List list) {
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rsSet = null;
		List<Map<String, Object>> listRS = new ArrayList<Map<String, Object>>();

		try {

			connection = getConnection();

			ps = connection.prepareStatement(sql);

			//设置参数
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
			}

			rsSet = ps.executeQuery();
			listRS = list(rsSet);

		} catch (SQLException e) {
			e.printStackTrace();
			return new ArrayList();
		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList();
		} finally {
			try {

				connList.add(connection);
				if (null != ps)
					ps.close();
				if (null != rsSet)
					rsSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return listRS;
	}

	public static List<Map<String, Object>> list(ResultSet rs) {
		if (null == rs) {
			return new ArrayList();
		} else {

			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
			Map<String, Object> map = null;
			;
			;
			try {
				while (rs.next()) {
					int ColumnCount = rs.getMetaData().getColumnCount();
					map = new HashMap<String, Object>();
					for (int i = 1; i < (ColumnCount + 1); i++) {
						map.put(rs.getMetaData().getColumnLabel(i)
								.toLowerCase(), rs.getObject(i));

					}
					list.add(map);

				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return list;
		}
	}

	/**
	 *    
	 * @param sql
	 * @param list
	 * @param iscommit
	 * @return
	 * 1.connection.setAutoCommit(false); 将自动提交设置为false 
	 * 2.保存点(SavePoint)
	 * JDBC定义了SavePoint接口，提供在一个更细粒度的事务控制机制。当设置了一个保存点后，可以rollback到该保存点处的状态，而不是rollback整个事务。
	 * Connection接口的setSavepoint和releaseSavepoint方法可以设置和释放保存点。
	 * JDBC规范虽然定义了事务的以上支持行为，但是各个JDBC驱动，数据库厂商对事务的支持程度可能各不相同。如果在程序中任意设置，可能得不到想要的效果。为此，JDBC提供了DatabaseMetaData接口，提供了一系列JDBC特性支持情况的获取方法。比如，通过DatabaseMetaData.supportsTransactionIsolationLevel方法可以判断对事务隔离级别的支持情况，通过DatabaseMetaData.supportsSavepoints方法可以判断对保存点的支持情况
	 */
	public Connection excute(String sql, List list, boolean iscommit) {
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rsSet = null;

		try {
			connection = getConnection();
			connection.setAutoCommit(false); //将自动提交设置为false   
			ps = connection.prepareStatement(sql);

			//设置参数
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
			}
			ps.execute();
			if (iscommit){
				connection.commit();
				if(!connection.getAutoCommit()){
					connection.setAutoCommit(true);// 恢复JDBC事务的默认提交方式 
				}
			}
		} catch (Exception e) {
			try {
				if (iscommit)
					connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return null;
		} finally {
			try {
				if (iscommit) {
					connection.close();
				}
				ps.clearParameters();
				rsSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}

		}
		return connection;
	}

	public int excute(String sql, List list, Connection con) {
		Connection connection = con;
		PreparedStatement ps = null;
		ResultSet rsSet = null;

		try {

			ps = connection.prepareStatement(sql);

			//设置参数
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
			}
			ps.execute();
			connection.commit();
			if(!connection.getAutoCommit())
			connection.setAutoCommit(true);// 恢复JDBC事务的默认提交方式 
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return -1;
		} finally {

			try {
				connection.close();
				ps.clearParameters();
				rsSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
				return -1;
			}

		}
		return 0;
	}
	public long countQuery(String sql, List list) {
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {

			connection = getConnection();

			ps = connection.prepareStatement(sql);

			//设置参数
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
			}

			rs = ps.executeQuery();	 
            while(rs.next()){
            	return rs.getLong(1);
            }
		} catch (SQLException e) {
			e.printStackTrace();
			 
		} catch (Exception e) {
			e.printStackTrace();
			 
		} finally {
			try {
				if (null != connection)
					connList.add(connection);
				if (null != rs)
					rs.close();
				if (null != ps)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return 0;
	}
	public static void main(String[] arg) throws SQLException {
		String sql = "SELECT * FROM t_punishment";
		List<Map<String, Object>> list = JdbcUtil.getListMap(sql,
				new ArrayList());
		System.out.print(list.size());
	}

}
